from io import BytesIO
from urllib.parse import quote

from django.core.paginator import Paginator
from django.db.models import Q
from django.db.transaction import get_connection
from django.http import HttpResponse, JsonResponse
from django.shortcuts import render

# Create your views here.
from django.utils import timezone
from openpyxl import Workbook

from CarQueryApp.models import Car, Record


def index(request):
    # object_list = []
    # car_list = Car.objects.all()
    # record_list = Record.objects.all()
    # object_list.append([car_list,record_list])
    # print(object_list)
    # context = {
    #     'object_list':object_list
    # }
    #
    return HttpResponse('测试成功')


def search(request):
    curr_page = int(request.GET.get('page', '1'))
    page_size = int(request.GET.get('PageSize', '5'))
    # curr_page = 1
    # page_size = 5
    carno = request.POST.get('carno', '')
    # 避免一加N查询
    queryset = Record.objects.filter(is_delete=False).defer('is_delete', 'deleted_time', 'update_time').select_related(
        'car').order_by('-makedate')
    if carno:
        # i 忽略大小写 contains两边模糊查询
        queryset = queryset.filter(Q(car__carno__icontains=carno) | Q(car__owner__icontains=carno))
    #     car = Car.objects.filter(carno=carno).first()
    # querset = Record.objects.filter(
    #     Q(car__carno__contains=carno) | Q(car__owner__contains=carno) )
    # car = querset[0].car
    # print(car)
    # 创建分页器对象
    paginator = Paginator(queryset, page_size)
    # 获取分页器对象的get_page方法指定页码获取对应的页对象
    page = paginator.get_page(curr_page)
    context = {
        'object_list': page.object_list,
        'current_page': page.number,
        'total_page': (paginator.count - 1) // page_size + 1,
        'has_prev': page.has_previous(),

        'has_next': page.has_next(),
        'prev_page': curr_page - 1,
        'next_page': curr_page + 1,
        'carno': carno

    }

    return render(request, 'carIndex.html', context=context)


def delet_record(request):
    '''删除记录'''
    rno = int(request.GET.get('rno', '0'))
    result = {
        'code': 10001,
        'msg': '操作失败'
    }
    if rno:
        record = Record.objects.filter(id=rno).first()
        if record and record.dealt:
            record.is_delete = True
            record.deleted_time = timezone.now()
            record.save()

            result = {
                'code': 10000,
                'msg': '操作成功'
            }
    return JsonResponse(result)


def handle_record(request):
    '''受理函数'''
    rno = int(request.GET.get('rno', '0'))
    result = {
        'code': 10001,
        'msg': '操作失败'
    }
    if rno:
        record = Record.objects.filter(id=rno).first()
        if record and not record.dealt:
            record.dealt = True

            record.save()

            result = {
                'code': 10000,
                'msg': '操作成功'
            }
    return JsonResponse(result)


def export_excel(request):
    '''下载excle'''
    carno = request.GET.get('carno', '')
    # 避免一加N查询
    queryset = Record.objects.filter(is_delete=False).defer('is_delete', 'deleted_time', 'update_time').select_related(
        'car').order_by('-makedate')
    if carno:
        # i 忽略大小写 contains两边模糊查询
        queryset = queryset.filter(Q(car__carno__icontains=carno) | Q(car__owner__icontains=carno))

    wb = Workbook()
    sheet = wb.active
    col_names = ('车牌号', '车主姓名', '违章原因', '违章时间', '处罚方式', '受理情况')
    for index, col in enumerate('ABCDEF'):
        sheet[f'{col}1'] = col_names[index]
    row = 2
    for record in queryset:
        sheet[f'A{row}'] = record.car.id
        sheet[f'B{row}'] = record.car.owner
        sheet[f'C{row}'] = record.reason
        sheet[f'D{row}'] = record.makedate
        sheet[f'E{row}'] = record.punish
        sheet[f'F{row}'] = '已受理' if record.dealt else '未受理'
        row += 1
    # BytesIO可变字节串
    buffer = BytesIO()

    wb.save(buffer)
    # 变成不变字节串
    resp = HttpResponse(buffer.getvalue(),
                        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

    filename = quote('违章记录表.xlsx')
    # inline 直接打开,一般不用
    resp['content-disposition'] = f'attachment; filename ="{filename}"'
    return resp

def get_bar_data(request):
    conn = get_connection()
    data = {}
    data1 = []
    with conn.cursor() as cursor:
        cursor.execute('select year(makedate) as ry, count(id) as total from tb_record group by ry')
        for row in cursor.fetchall():
            data['year'] = row[0]
            data['count'] = row[1]
            data1.append(data)
            data = {}
    context = {
        'data':data1,
        'status':200
    }
    return JsonResponse(context)
